import time
import numpy as np
import pymysql
import pandas as pd
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class MysqlUtils(object):
    def _init_(self)-> None:
        self.conn = pymysql.connect(
        host= '127.0.0.1',
        user='root',
        password='root',
        database='scenic',
        port= 3306, #明确指定端口
        charset='utf8mb4' #添加字符集设置
    )
        
    def get_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql= """
        SELEcr DArE(e-create_tine) as date, Hovk(g. creete_kine) as Nour, count(*) as count FROM order_userlgete_rel g WHERE HOUR (g.create _time) BETWEEN 6 and 23 
        GROUP BY date, hout
        """
        cursor.execute(sq1)
        ret = cursor.fetchal1()
        df = pd.DataFrame(ret)

        date_range = pd.date_range(start='2024-07-01', end='2025-03-02',freq='D')
        hours = range(6, 24)
        full_index = pd.MultiIndex.from_product([date_range, hours], names=['date','hour'])
        df_full = df.set_index(['date','hour']).reindex(full_index,fill_value = 0).reset_index()
        #按天组织数据，每行包含18个小时的检票次数
        df_pivot = df_full.pivot(index='date', columns='hour',valuess ='count')
        # print(df_pivot.head)
        df_pivot['dow'] = df_pivot.index.dayofweek #星期儿（0-6)
        df_pivot['month']= df_pivot.index.month #月份
        df_pivot['is_holiday']= df_pivot.index.map(self.is_holiday)
        print(df_pivot.head)

        #对星期几和月份进行独热编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow','month'], dtype=int)
        #归一化小时检票列
        hours_columns = list(range(6, 24))
        df_hours = df_pivot[hours_columns].copy()

        feature_columns =[col for col in df_pivot.colums if col not in hours_columns]
        df_feature = df_pivot[feature_columns].copy()

        scaler = MinMaxScaler()
        scaled_hours = scaler.fit_transform(df_hours)
        dump(scaler, 'NN/scaler.joblib')
        
        #将归一化后的数据转换为DataFrame
        df_hours_scaled = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours. index)
        
        #合并
        df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)
        print(df_pivot_clean.head)
        df_pivot_clean.to_csv('NN/scenic_data.csv', index=False)


if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()